from flask import Flask, render_template, jsonify, request
import socket
import threading
import time
import json
import os
from datetime import datetime, timedelta
import sqlite3
import queue
import requests
import re

app = Flask(__name__)

# OpenVPN管理接口配置 - 从环境变量读取
OPENVPN_HOST = os.environ.get('OPENVPN_HOST', '192.168.18.222')
OPENVPN_PORT = int(os.environ.get('OPENVPN_PORT', 8088))
OPENVPN_PASSWORD = os.environ.get('OPENVPN_PASSWORD', 'r3ach@BGFL#C')

# 数据库路径 - 使用容器内的data目录
DB_PATH = os.path.join('/app/data', 'openvpn_monitor.db')

# IP地理位置缓存
ip_location_cache = {}
ip_location_cache_file = os.path.join('/app/data', 'ip_location_cache.json')

def load_ip_location_cache():
    """加载IP地理位置缓存"""
    try:
        if os.path.exists(ip_location_cache_file):
            with open(ip_location_cache_file, 'r', encoding='utf-8') as f:
                return json.load(f)
    except Exception as e:
        print(f"加载IP地理位置缓存失败: {e}")
    return {}

def save_ip_location_cache():
    """保存IP地理位置缓存"""
    try:
        with open(ip_location_cache_file, 'w', encoding='utf-8') as f:
            json.dump(ip_location_cache, f, ensure_ascii=False, indent=2)
    except Exception as e:
        print(f"保存IP地理位置缓存失败: {e}")

def get_ip_location(ip_address):
    """获取IP地址的地理位置信息"""
    if not ip_address or ip_address == 'UNDEF':
        return None
    
    # 检查缓存
    if ip_address in ip_location_cache:
        return ip_location_cache[ip_address]
    
    try:
        # 使用免费的IP地理位置API
        response = requests.get(f'http://ip-api.com/json/{ip_address}?lang=zh-CN', timeout=5)
        if response.status_code == 200:
            data = response.json()
            if data.get('status') == 'success':
                location_info = {
                    'country': data.get('country', ''),
                    'region': data.get('regionName', ''),
                    'city': data.get('city', ''),
                    'isp': data.get('isp', ''),
                    'timezone': data.get('timezone', '')
                }
                
                # 构建地理位置描述
                location_parts = []
                if location_info['city']:
                    location_parts.append(location_info['city'])
                if location_info['region']:
                    location_parts.append(location_info['region'])
                if location_info['country']:
                    location_parts.append(location_info['country'])
                
                location_desc = ' - '.join(location_parts) if location_parts else '未知地区'
                
                result = {
                    'location': location_desc,
                    'details': location_info
                }
                
                # 缓存结果
                ip_location_cache[ip_address] = result
                save_ip_location_cache()
                
                return result
    except Exception as e:
        print(f"获取IP地理位置失败 {ip_address}: {e}")
    
    # 如果获取失败，返回默认值并缓存
    default_result = {'location': '未知地区', 'details': {}}
    ip_location_cache[ip_address] = default_result
    save_ip_location_cache()
    
    return default_result

def extract_ip_from_address(address):
    """从地址中提取IP地址"""
    if not address:
        return None
    
    # 匹配IP地址模式 (支持IPv4和IPv6)
    ip_pattern = r'(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|[0-9a-fA-F:]+:[0-9a-fA-F:]+)'
    match = re.search(ip_pattern, address)
    if match:
        return match.group(1)
    return None

# 数据库初始化
def init_database():
    # 确保data目录存在
    os.makedirs('/app/data', exist_ok=True)
    
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    # 创建在线用户表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS online_users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL,
            virtual_address TEXT NOT NULL,
            real_address TEXT,
            location TEXT,
            bytes_received INTEGER DEFAULT 0,
            bytes_sent INTEGER DEFAULT 0,
            connected_since TEXT NOT NULL,
            last_updated TEXT NOT NULL
        )
    ''')
    
    # 创建历史记录表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS connection_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL,
            virtual_address TEXT NOT NULL,
            real_address TEXT,
            location TEXT,
            bytes_received INTEGER DEFAULT 0,
            bytes_sent INTEGER DEFAULT 0,
            connected_since TEXT NOT NULL,
            disconnected_at TEXT NOT NULL,
            total_duration INTEGER DEFAULT 0
        )
    ''')
    
    # 检查是否需要添加新列
    try:
        cursor.execute("SELECT real_address FROM online_users LIMIT 1")
    except sqlite3.OperationalError:
        cursor.execute("ALTER TABLE online_users ADD COLUMN real_address TEXT")
        cursor.execute("ALTER TABLE online_users ADD COLUMN location TEXT")
    
    try:
        cursor.execute("SELECT real_address FROM connection_history LIMIT 1")
    except sqlite3.OperationalError:
        cursor.execute("ALTER TABLE connection_history ADD COLUMN real_address TEXT")
        cursor.execute("ALTER TABLE connection_history ADD COLUMN location TEXT")
    
    conn.commit()
    conn.close()

# OpenVPN管理接口类
class OpenVPNManager:
    def __init__(self, host, port, password):
        self.host = host
        self.port = port
        self.password = password
        self.socket = None
        self.connected = False
        
    def connect(self):
        try:
            self.socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
            self.socket.settimeout(10)
            self.socket.connect((self.host, self.port))
            
            # 持续读取直到出现ENTER PASSWORD
            response = self._recv_until(["ENTER PASSWORD", "SUCCESS"])
            if 'ENTER PASSWORD' in response:
                self.socket.send((self.password + '\n').encode('utf-8'))
                response = self._recv_until(["SUCCESS", "ERROR"])
                if 'SUCCESS' in response:
                    self.connected = True
                    return True
                else:
                    print("密码验证失败: ", response)
            elif 'SUCCESS' in response:
                self.connected = True
                return True
            else:
                print("未收到密码提示: ", response)
            return False
        except Exception as e:
            print(f"连接OpenVPN管理接口失败: {e}")
            self.disconnect()
            return False
    
    def disconnect(self):
        if self.socket:
            try:
                self.socket.close()
            except:
                pass
        self.connected = False
    
    def send_command(self, command):
        if not self.connected:
            if not self.connect():
                return None
        
        try:
            self.socket.send((command + '\n').encode('utf-8'))
            response = self._recv_until(["END"])
            return response
        except Exception as e:
            print(f"发送命令失败: {e}")
            self.connected = False
            self.disconnect()
            return None
    
    def _recv_until(self, end_markers, timeout=10):
        """
        持续读取socket直到遇到end_markers中的任意一个字符串或超时
        """
        self.socket.settimeout(timeout)
        data = b''
        start_time = time.time()
        while True:
            try:
                chunk = self.socket.recv(4096)
                if not chunk:
                    break
                data += chunk
                text = data.decode('utf-8', errors='ignore')
                for marker in end_markers:
                    if marker in text:
                        return text
                if time.time() - start_time > timeout:
                    break
            except socket.timeout:
                break
            except Exception as e:
                print(f"读取数据异常: {e}")
                break
        return data.decode('utf-8', errors='ignore')
    
    def get_status(self):
        response = self.send_command('status')
        if not response:
            return []
        
        lines = response.strip().split('\n')
        clients = []
        
        # 解析状态信息
        for line in lines:
            if line.startswith('OpenVPN CLIENT LIST'):
                continue
            if line.startswith('Common Name'):
                continue
            if line.startswith('ROUTING TABLE'):
                break
            if line.startswith('GLOBAL STATS'):
                break
            
            # 尝试CSV格式解析
            parts = line.split(',')
            if len(parts) >= 5:
                try:
                    # 验证前4个字段
                    username = parts[0].strip()
                    virtual_address = parts[1].strip()
                    
                    # 验证字节数字段
                    if not parts[2].strip().isdigit() or not parts[3].strip().isdigit():
                        continue
                    
                    bytes_received = int(parts[2])
                    bytes_sent = int(parts[3])
                    
                    # 连接时间可能在后面的字段中
                    connected_since = None
                    for i in range(4, min(len(parts), 8)):  # 检查前8个字段
                        time_part = parts[i].strip()
                        # 检查是否是时间格式
                        if ' ' in time_part and len(time_part) > 10:
                            connected_since = time_part
                            break
                    
                    if connected_since:
                        client = {
                            'username': username,
                            'virtual_address': virtual_address,
                            'bytes_received': bytes_received,
                            'bytes_sent': bytes_sent,
                            'connected_since': connected_since,
                            'last_updated': datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                        }
                        clients.append(client)
                        
                except (ValueError, IndexError):
                    continue
            
            # 尝试空格分隔格式解析
            else:
                space_parts = line.split()
                if len(space_parts) >= 6:
                    try:
                        # 假设格式: username virtual_ip virtual_port bytes_received bytes_sent time
                        username = space_parts[0]
                        
                        # 检查是否是IP地址格式
                        if '.' in space_parts[1] and ':' in space_parts[2]:
                            virtual_address = f"{space_parts[1]}:{space_parts[2]}"
                            bytes_received = int(space_parts[3])
                            bytes_sent = int(space_parts[4])
                            
                            # 连接时间可能是后面的字段组合
                            time_parts = space_parts[5:]
                            connected_since = ' '.join(time_parts)
                            
                            client = {
                                'username': username,
                                'virtual_address': virtual_address,
                                'bytes_received': bytes_received,
                                'bytes_sent': bytes_sent,
                                'connected_since': connected_since,
                                'last_updated': datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                            }
                            clients.append(client)
                            
                    except (ValueError, IndexError):
                        continue
        
        return clients
    


# 全局OpenVPN管理器实例
ovpn_manager = OpenVPNManager(OPENVPN_HOST, OPENVPN_PORT, OPENVPN_PASSWORD)

# 存储上一次的在线用户列表，用于检测离线用户
last_online_users = {}

# 数据库锁和队列
db_lock = threading.Lock()
history_queue = queue.Queue()

# 数据同步线程
def sync_data():
    global last_online_users
    
    # 加载IP地理位置缓存
    global ip_location_cache
    ip_location_cache = load_ip_location_cache()
    
    while True:
        try:
            clients = ovpn_manager.get_status()
            current_online_users = {}
            
            if clients:
                with db_lock:
                    # 更新在线用户数据
                    conn = sqlite3.connect(DB_PATH, timeout=30.0)
                    cursor = conn.cursor()
                    
                    # 清空当前在线用户
                    cursor.execute('DELETE FROM online_users')
                    
                    # 插入新的在线用户并记录当前在线用户
                    for client in clients:
                        if client['username'] == 'UNDEF':
                            continue
                        
                        # 获取地理位置信息
                        real_address = None
                        location = None
                        
                        # 从虚拟地址中提取真实IP（如果有的话）
                        # 通常OpenVPN的虚拟地址格式是 10.8.0.x，我们需要获取真实IP
                        # 这里我们暂时使用虚拟地址，实际应用中可能需要从OpenVPN获取真实IP
                        virtual_ip = extract_ip_from_address(client['virtual_address'])
                        
                        # 获取地理位置信息
                        if virtual_ip:
                            location_info = get_ip_location(virtual_ip)
                            if location_info:
                                location = location_info['location']
                        
                        cursor.execute('''
                            INSERT INTO online_users 
                            (username, virtual_address, real_address, location, bytes_received, bytes_sent, connected_since, last_updated)
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                        ''', (
                            client['username'], client['virtual_address'], real_address, location,
                            client['bytes_received'], client['bytes_sent'],
                            client['connected_since'], client['last_updated']
                        ))
                        
                        # 记录当前在线用户
                        current_online_users[client['username']] = {
                            'virtual_address': client['virtual_address'],
                            'real_address': real_address,
                            'location': location,
                            'bytes_received': client['bytes_received'],
                            'bytes_sent': client['bytes_sent'],
                            'connected_since': client['connected_since'],
                            'last_updated': client['last_updated']
                        }
                    
                    conn.commit()
                    conn.close()
                
                # 检测离线用户并保存历史记录
                for username, user_data in last_online_users.items():
                    if username not in current_online_users:
                        if username == 'UNDEF':
                            continue
                        history_queue.put((username, user_data))
                
                # 更新上一次在线用户列表
                last_online_users = current_online_users.copy()
                
        except Exception as e:
            print(f"数据同步错误: {e}")
        
        time.sleep(30)  # 每30秒同步一次

# 历史记录处理线程
def process_history_queue():
    while True:
        try:
            # 从队列中获取历史记录
            username, user_data = history_queue.get(timeout=1)
            save_connection_history(username, user_data)
            history_queue.task_done()
        except queue.Empty:
            continue
        except Exception as e:
            print(f"处理历史记录队列错误: {e}")
            time.sleep(1)

def save_connection_history(username, user_data):
    """保存用户连接历史记录"""
    try:
        if username == 'UNDEF':
            return
        # 解析连接开始时间
        connected_time_str = user_data['connected_since']
        connected_time = None
        
        # 处理数字时间格式（OpenVPN可能返回数字）
        if isinstance(connected_time_str, (int, float)):
            # 如果是数字，假设是Unix时间戳
            try:
                connected_time = datetime.fromtimestamp(connected_time_str)
            except (ValueError, OSError):
                # 如果不是Unix时间戳，尝试其他解析方式
                connected_time_str = str(connected_time_str)
        
        # 如果是字符串，尝试多种时间格式
        if isinstance(connected_time_str, str):
            time_formats = [
                '%Y-%m-%d %H:%M:%S',  # 标准格式
                '%Y-%m-%d %H:%M',    # 无秒格式
                '%m/%d/%Y %H:%M:%S', # 美式格式
                '%d/%m/%Y %H:%M:%S', # 欧式格式
                '%Y-%m-%dT%H:%M:%S', # ISO格式
                '%a %b %d %H:%M:%S %Y',  # OpenVPN格式: Tue Jul  1 11:39:02 2025
                '%a %b  %d %H:%M:%S %Y', # OpenVPN格式(双空格): Tue Jul  1 11:39:02 2025
            ]
            
            for fmt in time_formats:
                try:
                    connected_time = datetime.strptime(connected_time_str, fmt)
                    break
                except ValueError:
                    continue
        
        if connected_time:
            # 计算连接时长
            disconnected_time = datetime.now()
            duration = disconnected_time - connected_time
            total_seconds = int(duration.total_seconds())
            
            # 使用锁保护数据库写入
            with db_lock:
                conn = sqlite3.connect(DB_PATH, timeout=30.0)
                cursor = conn.cursor()
                
                cursor.execute('''
                    INSERT INTO connection_history 
                    (username, virtual_address, real_address, location, bytes_received, bytes_sent, 
                     connected_since, disconnected_at, total_duration)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (
                    username, user_data['virtual_address'],
                    user_data.get('real_address'), user_data.get('location'),
                    user_data['bytes_received'], user_data['bytes_sent'],
                    connected_time.strftime('%Y-%m-%d %H:%M:%S'),
                    disconnected_time.strftime('%Y-%m-%d %H:%M:%S'),
                    total_seconds
                ))
                
                conn.commit()
                conn.close()
            
            print(f"保存用户 {username} 的连接历史记录，连接时长: {total_seconds}秒")
            
    except Exception as e:
        print(f"保存连接历史记录失败: {e}")

# 启动数据同步线程
sync_thread = threading.Thread(target=sync_data, daemon=True)
sync_thread.start()

# 启动历史记录处理线程
history_thread = threading.Thread(target=process_history_queue, daemon=True)
history_thread.start()

@app.route('/')
def index():
    return render_template('index.html', config={
        'OPENVPN_HOST': OPENVPN_HOST,
        'OPENVPN_PORT': OPENVPN_PORT
    })

@app.route('/online')
def online():
    return render_template('online.html', config={
        'OPENVPN_HOST': OPENVPN_HOST,
        'OPENVPN_PORT': OPENVPN_PORT
    })

@app.route('/history')
def history():
    return render_template('history.html', config={
        'OPENVPN_HOST': OPENVPN_HOST,
        'OPENVPN_PORT': OPENVPN_PORT
    })

@app.route('/test')
def test():
    return render_template('test_display.html')

@app.route('/simple')
def simple():
    return render_template('test_simple.html')



@app.route('/api/online-users')
def api_online_users():
    try:
        with db_lock:
            conn = sqlite3.connect(DB_PATH, timeout=30.0)
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT username, virtual_address, real_address, location, bytes_received, bytes_sent, 
                       connected_since, last_updated
                FROM online_users
                ORDER BY username
            ''')
            
            users = []
            for row in cursor.fetchall():
                user = {
                    'username': row[0],
                    'virtual_address': row[1],
                    'real_address': row[2],
                    'location': row[3],
                    'bytes_received': row[4],
                    'bytes_sent': row[5],
                    'connected_since': row[6],
                    'last_updated': row[7]
                }
                
                # 计算在线时长
                try:
                    # OpenVPN返回的时间格式可能是多种格式，尝试解析
                    connected_time_str = row[6]  # connected_since
                    connected_time = None
                    
                    # 处理数字时间格式（OpenVPN可能返回数字）
                    if isinstance(connected_time_str, (int, float)):
                        # 如果是数字，假设是Unix时间戳
                        try:
                            connected_time = datetime.fromtimestamp(connected_time_str)
                        except (ValueError, OSError):
                            # 如果不是Unix时间戳，尝试其他解析方式
                            connected_time_str = str(connected_time_str)
                    
                    # 如果是字符串，尝试多种时间格式
                    if isinstance(connected_time_str, str):
                        time_formats = [
                            '%Y-%m-%d %H:%M:%S',  # 标准格式
                            '%Y-%m-%d %H:%M',    # 无秒格式
                            '%m/%d/%Y %H:%M:%S', # 美式格式
                            '%d/%m/%Y %H:%M:%S', # 欧式格式
                            '%Y-%m-%dT%H:%M:%S', # ISO格式
                            '%a %b %d %H:%M:%S %Y',  # OpenVPN格式: Tue Jul  1 11:39:02 2025
                            '%a %b  %d %H:%M:%S %Y', # OpenVPN格式(双空格): Tue Jul  1 11:39:02 2025
                        ]
                        
                        for fmt in time_formats:
                            try:
                                connected_time = datetime.strptime(connected_time_str, fmt)
                                break
                            except ValueError:
                                continue
                    
                    if connected_time:
                        duration = datetime.now() - connected_time
                        total_seconds = int(duration.total_seconds())
                        
                        # 格式化显示时长
                        hours = total_seconds // 3600
                        minutes = (total_seconds % 3600) // 60
                        seconds = total_seconds % 60
                        
                        if hours > 0:
                            user['duration'] = f"{hours}小时{minutes}分钟{seconds}秒"
                        elif minutes > 0:
                            user['duration'] = f"{minutes}分钟{seconds}秒"
                        else:
                            user['duration'] = f"{seconds}秒"
                    else:
                        user['duration'] = '时间格式未知'
                        
                except Exception as e:
                    print(f"解析连接时间失败: {e}, 原始时间: {row[6]}")
                    user['duration'] = '解析失败'
                
                # 格式化流量
                user['traffic_received'] = format_bytes(row[4])  # bytes_received
                user['traffic_sent'] = format_bytes(row[5])      # bytes_sent
                
                users.append(user)
            
            conn.close()
            return jsonify(users)
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/connection-history')
def api_connection_history():
    try:
        # 获取查询参数
        username = request.args.get('username', '').strip()
        start_date = request.args.get('start_date', '').strip()
        end_date = request.args.get('end_date', '').strip()
        page = int(request.args.get('page', 1))
        per_page = int(request.args.get('per_page', 10))
        if per_page not in [10, 50, 100]:
            per_page = 10
        offset = (page - 1) * per_page
        
        with db_lock:
            conn = sqlite3.connect(DB_PATH, timeout=30.0)
            cursor = conn.cursor()
            
            # 构建查询条件
            where_conditions = ["username != 'UNDEF'"]
            params = []
            
            if username:
                where_conditions.append("username LIKE ?")
                params.append(f'%{username}%')
            
            if start_date:
                where_conditions.append("disconnected_at >= ?")
                params.append(f'{start_date} 00:00:00')
            
            if end_date:
                where_conditions.append("disconnected_at <= ?")
                params.append(f'{end_date} 23:59:59')
            
            # 统计总数
            count_sql = 'SELECT COUNT(*) FROM connection_history'
            if where_conditions:
                count_sql += ' WHERE ' + ' AND '.join(where_conditions)
            cursor.execute(count_sql, params)
            total = cursor.fetchone()[0]
            total_pages = (total + per_page - 1) // per_page
            
            # 构建SQL查询
            sql = '''
                SELECT username, virtual_address, real_address, location, bytes_received, bytes_sent,
                       connected_since, disconnected_at, total_duration
                FROM connection_history
            '''
            if where_conditions:
                sql += ' WHERE ' + ' AND '.join(where_conditions)
            sql += ' ORDER BY disconnected_at DESC'
            sql += f' LIMIT {per_page} OFFSET {offset}'
            
            cursor.execute(sql, params)
            
            history = []
            for row in cursor.fetchall():
                record = {
                    'username': row[0],
                    'virtual_address': row[1],
                    'real_address': row[2],
                    'location': row[3],
                    'bytes_received': row[4],
                    'bytes_sent': row[5],
                    'connected_since': row[6],
                    'disconnected_at': row[7],
                    'total_duration': row[8]
                }
                # 格式化流量
                record['traffic_received'] = format_bytes(row[4])
                record['traffic_sent'] = format_bytes(row[5])
                # 格式化时长
                if row[8]:
                    record['duration_formatted'] = format_duration(row[8])
                else:
                    record['duration_formatted'] = '未知'
                history.append(record)
            conn.close()
            return jsonify({
                'total': total,
                'total_pages': total_pages,
                'page': page,
                'per_page': per_page,
                'data': history
            })
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/connection-stats')
def api_connection_stats():
    """获取连接统计信息"""
    try:
        # 获取查询参数
        username = request.args.get('username', '').strip()
        start_date = request.args.get('start_date', '').strip()
        end_date = request.args.get('end_date', '').strip()
        
        with db_lock:
            conn = sqlite3.connect(DB_PATH, timeout=30.0)
            cursor = conn.cursor()
            
            # 构建查询条件
            where_conditions = ["username != 'UNDEF'"]
            params = []
            
            if username:
                where_conditions.append("username LIKE ?")
                params.append(f'%{username}%')
            
            if start_date:
                where_conditions.append("disconnected_at >= ?")
                params.append(f'{start_date} 00:00:00')
            
            if end_date:
                where_conditions.append("disconnected_at <= ?")
                params.append(f'{end_date} 23:59:59')
            
            # 构建SQL查询
            sql = '''
                SELECT 
                    COUNT(*) as total_connections,
                    COUNT(DISTINCT username) as unique_users,
                    SUM(bytes_received) as total_received,
                    SUM(bytes_sent) as total_sent,
                    SUM(total_duration) as total_duration,
                    AVG(total_duration) as avg_duration
                FROM connection_history
            '''
            
            if where_conditions:
                sql += ' WHERE ' + ' AND '.join(where_conditions)
            
            cursor.execute(sql, params)
            row = cursor.fetchone()
            
            if row:
                stats = {
                    'total_connections': row[0] or 0,
                    'unique_users': row[1] or 0,
                    'total_received': row[2] or 0,
                    'total_sent': row[3] or 0,
                    'total_duration': row[4] or 0,
                    'avg_duration': row[5] or 0,
                    'total_traffic': (row[2] or 0) + (row[3] or 0)
                }
                
                # 格式化数据
                stats['total_received_formatted'] = format_bytes(stats['total_received'])
                stats['total_sent_formatted'] = format_bytes(stats['total_sent'])
                stats['total_traffic_formatted'] = format_bytes(stats['total_traffic'])
                stats['total_duration_formatted'] = format_duration(stats['total_duration'])
                stats['avg_duration_formatted'] = format_duration(int(stats['avg_duration']) if stats['avg_duration'] is not None else 0)
            else:
                stats = {
                    'total_connections': 0,
                    'unique_users': 0,
                    'total_received': 0,
                    'total_sent': 0,
                    'total_duration': 0,
                    'avg_duration': 0,
                    'total_traffic': 0,
                    'total_received_formatted': '0 B',
                    'total_sent_formatted': '0 B',
                    'total_traffic_formatted': '0 B',
                    'total_duration_formatted': '0秒',
                    'avg_duration_formatted': '0秒'
                }
            
            conn.close()
            return jsonify(stats)
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/user-stats')
def api_user_stats():
    """获取单个用户的统计信息"""
    try:
        username = request.args.get('username', '').strip()
        start_date = request.args.get('start_date', '').strip()
        end_date = request.args.get('end_date', '').strip()
        
        if not username:
            return jsonify({'error': '用户名不能为空'}), 400
        
        with db_lock:
            conn = sqlite3.connect(DB_PATH, timeout=30.0)
            cursor = conn.cursor()
            
            # 构建查询条件
            where_conditions = ["username = ?"]
            params = [username]
            
            if start_date:
                where_conditions.append("disconnected_at >= ?")
                params.append(f'{start_date} 00:00:00')
            
            if end_date:
                where_conditions.append("disconnected_at <= ?")
                params.append(f'{end_date} 23:59:59')
            
            # 构建SQL查询
            sql = '''
                SELECT 
                    COUNT(*) as total_connections,
                    SUM(bytes_received) as total_received,
                    SUM(bytes_sent) as total_sent,
                    SUM(total_duration) as total_duration,
                    AVG(total_duration) as avg_duration,
                    MIN(connected_since) as first_connection,
                    MAX(disconnected_at) as last_connection
                FROM connection_history
                WHERE ''' + ' AND '.join(where_conditions)
            
            cursor.execute(sql, params)
            row = cursor.fetchone()
            
            if row:
                stats = {
                    'username': username,
                    'total_connections': row[0] or 0,
                    'total_received': row[1] or 0,
                    'total_sent': row[2] or 0,
                    'total_duration': row[3] or 0,
                    'avg_duration': row[4] or 0,
                    'first_connection': row[5],
                    'last_connection': row[6],
                    'total_traffic': (row[1] or 0) + (row[2] or 0)
                }
                
                # 格式化数据
                stats['total_received_formatted'] = format_bytes(stats['total_received'])
                stats['total_sent_formatted'] = format_bytes(stats['total_sent'])
                stats['total_traffic_formatted'] = format_bytes(stats['total_traffic'])
                stats['total_duration_formatted'] = format_duration(stats['total_duration'])
                stats['avg_duration_formatted'] = format_duration(int(stats['avg_duration']) if stats['avg_duration'] is not None else 0)
            else:
                stats = {
                    'username': username,
                    'total_connections': 0,
                    'total_received': 0,
                    'total_sent': 0,
                    'total_duration': 0,
                    'avg_duration': 0,
                    'first_connection': None,
                    'last_connection': None,
                    'total_traffic': 0,
                    'total_received_formatted': '0 B',
                    'total_sent_formatted': '0 B',
                    'total_traffic_formatted': '0 B',
                    'total_duration_formatted': '0秒',
                    'avg_duration_formatted': '0秒'
                }
            
            conn.close()
            return jsonify(stats)
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/update-location/<ip_address>')
def api_update_location(ip_address):
    """手动更新IP地址的地理位置信息"""
    try:
        if not ip_address or ip_address == 'UNDEF':
            return jsonify({'error': '无效的IP地址'}), 400
        
        # 获取地理位置信息
        location_info = get_ip_location(ip_address)
        
        if location_info:
            return jsonify({
                'success': True,
                'ip_address': ip_address,
                'location': location_info['location'],
                'details': location_info['details']
            })
        else:
            return jsonify({'error': '无法获取地理位置信息'}), 500
            
    except Exception as e:
        return jsonify({'error': str(e)}), 500


def format_bytes(bytes_value):
    """格式化字节数为可读格式"""
    if bytes_value == 0:
        return "0 B"
    
    size_names = ["B", "KB", "MB", "GB", "TB"]
    i = 0
    while bytes_value >= 1024 and i < len(size_names) - 1:
        bytes_value /= 1024.0
        i += 1
    
    return f"{bytes_value:.2f} {size_names[i]}"

def format_duration(seconds):
    """格式化秒数为可读格式"""
    if seconds is None or seconds == 0:
        return "未知"
    
    try:
        seconds = int(seconds)
        hours = seconds // 3600
        minutes = (seconds % 3600) // 60
        secs = seconds % 60
        
        if hours > 0:
            return f"{hours}小时{minutes}分钟{secs}秒"
        elif minutes > 0:
            return f"{minutes}分钟{secs}秒"
        else:
            return f"{secs}秒"
    except (ValueError, TypeError):
        return "未知"

if __name__ == '__main__':
    # 初始化数据库
    init_database()
    
    # 创建templates目录
    os.makedirs('templates', exist_ok=True)
    os.makedirs('static', exist_ok=True)
    os.makedirs('static/css', exist_ok=True)
    os.makedirs('static/js', exist_ok=True)
    
    app.run(debug=True, host='0.0.0.0', port=5000)
